#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2017-06-28
# @Author  : zhonglitao
# @Remark  : 系统通用函数方法，便于统一调用

import web_XZDQ_V1_0.MySQLDB as MySQLDB


# 功能：生成编号
# 输入：表名,序号
# 输出：编号
# 注意：
# 1、首先需要在 t_sys_number 中设定规则
# 2、若传入的表名无对应规则，则生成一14位随机编号
def GenDocNum(tableName, tableSeq):
    strd = "genDocNum('%s',%s);" % (tableName, tableSeq)
    row = MySQLDB.ProdMySql(strd)
    docNum = ''
    try:
        if len(row) > 0:
            docNum = row[0][0]
    except:
        docNum = ''
    return docNum


def ConverToDICT(datas, keys):
    """
        :param datas: 数据集<tuple>
        :param keys:  数据集对应属性名，切记位置不可写错
    """
    return [dict(zip(keys, w)) for w in datas]


# 功能：获取param信息
# 输入：paramcode
# 输出：数组
def GetParamInfoByCode(paramCode):
    list = []
    try:
        row = MySQLDB.SelectMySql(
            "select a.param_id,a.param_name from t_sys_param a where exists(select 1 from t_sys_param b where a.parent_id = b.param_id and b.param_code = '%s') and status=1 order by orderby;" % paramCode)
        if len(row) > 0:
            for i in range(len(row)):
                info = {}
                info["id"] = row[i][0]
                info["name"] = row[i][1]
                list.append(info)
    except Exception as e:
        pass
    return list


# 功能：获取路线信息
# 输入：paramcode
# 输出：数组
def GetLineInfoList():
    list = []
    try:
        row = MySQLDB.SelectMySql("select line_id,line_name from t_sys_line where status = 1;")
        if len(row) > 0:
            for i in range(len(row)):
                info = {}
                info["id"] = row[i][0]
                info["name"] = row[i][1]
                list.append(info)
    except Exception as e:
        pass
    return list


# 功能：获取路段信息
# 输入：paramcode
# 输出：数组
def GetSectionInfoList():
    list = []
    try:
        row = MySQLDB.SelectMySql("select section_id,section_name,line_id from t_sys_section where  status=1;")
        if len(row) > 0:
            for i in range(len(row)):
                info = {}
                info["id"] = row[i][0]
                info["name"] = row[i][1]
                info["lineId"] = row[i][2]
                list.append(info)
    except Exception as e:
        pass
    return list


# 功能：获取构件类型
# 输入：parts_code
# 输出：数组
def PartstypeInfoList(parts_code):
    list = []
    try:
        row = MySQLDB.SelectMySql("select parts_code,parts_name from t_sys_parts where parts_code =%s;" % parts_code)
        if len(row) > 0:
            for i in range(len(row)):
                info = {}
                info["id"] = row[i][0]
                info["name"] = row[i][1]
                list.append(info)
    except Exception as e:
        pass
    return list


# 功能：根据桥梁id，取得桥梁部件
# 输入：bridgeId
# 输出：数组
def GetPartsListByBridge(bridgeId):
    row = MySQLDB.SelectMySql(
        "select parts_no,parts_code,bridge_parts_name,position_code,param_bridge_type_code,bridge_parts_id from t_sys_bridge_parts where exists(select 1 from (select param_code from t_sys_param a where param_id in (select distinct(param_bridge_type) from t_bridge_upperpart where bridge_id = %s group by param_bridge_type) group by a.param_id limit 1) t where t.param_code = param_bridge_type_code);" % bridgeId)
    return row


def GetDiseaseByMemberId(memberId):
    row = MySQLDB.SelectMySql("call getDiseaseByMemberId(%s);" % memberId)
    return row


def GetPositionByDisease(memberId, diseaseId):
    return MySQLDB.SelectMySql("call getPositionByDisease(%s,%s);" % (memberId, diseaseId))


def GetDiseaseLevel(diseaseId):
    return MySQLDB.SelectMySql(
        "select disease_scale_id,level,sumup from t_sys_disease_scale where disease_id = '%s' order by level" % diseaseId)


def GetTaskDiseaseById(taskdiseaseid):
    return MySQLDB.SelectMySql(
        "select a.`task_disease_id`,a.`task_id`,a.`code`,a.`weather`,a.`temperature`,a.`urgency_level`,a.`param_partol_id`,a.`disease_id`,a.`disease_name`,a.`pile_direction`,a.`pile_km`,a.`pile_m`,a.`remark`,b.bridge_name from t_dc_task_disease a left join t_bridge_base b on a.bridge_id = b.bridge_id where task_disease_id = %s;" % taskdiseaseid)


def GetDiseaseByCheckItem(checkitemid):
    return MySQLDB.SelectMySql(
        "select b.disease_id,disease_name from t_disease_checkitem a left join t_sys_disease b on a.disease_id = b.disease_id where param_dailycheck_id = %s and status = 1 order by b.orderby asc;" % checkitemid)


def GetDiseaseFileById(taskdiseaseid):
    return MySQLDB.SelectMySql(
        "select task_disease_file_id,task_disease_id,name,url,type,description,create_user,create_date,update_user,update_date from t_dc_task_disease_file where task_disease_id = %s and status = 1;" % taskdiseaseid)


def GetRCJCRecordMedia(taskdiseaseid):
    return MySQLDB.SelectMySql(
        "select task_disease_file_id, task_disease_id, name, url, type, description, create_user, create_date, update_user, update_date from t_dc_task_disease_file a where task_disease_id = %s and status =1 ;" % taskdiseaseid)


def GetRCXJTaskInfo(taskid):
    return MySQLDB.SelectMySql(
        "select task_id,content,task_date,type,status,weather,temperature,charge_dept,charge_user from t_dc_task where task_id = %s;" % taskid)


def GetJCJCList():
    return MySQLDB.SelectMySql(
        "SELECT check_id,bridge_id,bridge_name,bridge_code,check_depart,check_depart_name,user_charge_name, date_format(from_date,'%Y-%m-%d') as from_date, date_format(to_date,'%Y-%m-%d') as to_date ,score,grade,status,getJCJCDiseaseCount(check_id),(select group_concat(parts_sub_name) from t_check_often_items where check_id = a.check_id and status = 1 group by check_id) checkitems from t_check_often a where status <> 0;")


def GetPartsSub(bridgetype, partscode):
    return MySQLDB.SelectMySql(
        "select parts_sub_id,param_bridge_type_code,parts_code,position_code,name from t_sys_parts_sub where param_bridge_type_code = '%s' and parts_code = '%s' and status = 1;" % (
            bridgetype, partscode))


def GetJCJCData(checkid):
    return MySQLDB.SelectMySql(
        "select check_id,bridge_id,bridge_name,bridge_code,check_depart,check_depart_name,user_charge_name, date_format(from_date,'%%Y-%%m-%%d') as from_date, date_format(to_date,'%%Y-%%m-%%d') as to_date ,score,grade,status  from t_check_often where check_id= '%s' and status = 1;" % (
            checkid))


def GetJCJCPartSub(checkid):
    return MySQLDB.SelectMySql(
        "select a.check_item_id,a.check_id,a.parts_code,a.parts_sub_id,a.parts_sub_name,a.status,b.bridge_parts_id from t_check_often_items a inner join t_sys_bridge_parts b on a.param_bridge_type_code = b.param_bridge_type_code and a.parts_code = b.parts_code where check_id = '%s' and status = 1;" % (
            checkid))


def GetBridgeType(bridgeid):
    return MySQLDB.SelectMySql(
        "select param_code,param_name,param_id from t_sys_param where param_id in (select distinct(param_bridge_type) from t_bridge_upperpart where bridge_id = %s group by param_bridge_type);" % bridgeid)


def GetBridgeMemberByBridgeId(bridgeid):
    return MySQLDB.SelectMySql(
        "select bridge_member_id,member_name,notes,parts_code,parts_sub_id from t_bridge_member where status = 1 and bridge_id = %s;" % bridgeid)


def GetBridgeMemberByPartssub(bridgeid, partssubid):
    return MySQLDB.SelectMySql(
        "select bridge_member_id,member_name,notes,parts_code,parts_sub_id from t_bridge_member where status = 1 and bridge_id = %s and parts_sub_id = %s;" % (
            bridgeid, partssubid))


def GetBridgeMemberDisease(bridgeid):
    return MySQLDB.SelectMySql(
        "select m.bridge_member_id,m.member_name,m.notes,m.parts_code,m.parts_sub_id,ifnull(s.diseaseCount,0) disease_count from t_bridge_member m left join (select distinct(bridge_id),member_id,count(1) diseaseCount from t_check_often_record where bridge_id=%s and status=1 group by bridge_id,member_id) s on m.bridge_member_id = s.member_id where m.bridge_id = %s and m.status=1;" % (
            bridgeid, bridgeid))


def GetBridgeMemberDiseaseByDate(bridgeid, fdate, tdate):
    return MySQLDB.SelectMySql(
        "select m.bridge_member_id,m.member_name,m.notes,m.parts_code,m.parts_sub_id,ifnull(s.diseaseCount,0) disease_count from t_bridge_member m left join (select distinct(bridge_id),member_id,count(1) diseaseCount from t_check_often_record where bridge_id=%s and status=1 and check_date between '%s' and '%s' group by bridge_id,member_id) s on m.bridge_member_id = s.member_id where m.bridge_id = %s and m.status=1;" % (
            bridgeid, fdate, tdate, bridgeid))


def GetAllBridge():
    return MySQLDB.SelectMySql("select bridge_id,bridge_code,bridge_name from t_bridge_base;")
